﻿Imports System.Data.OleDb

Public Class MonHocDAO
    Public Shared Function SelectAll() As List(Of MonHocDTO)
        Const strSql As String = "select * from MONHOC"
        Dim ds = New List(Of MonHocDTO)
        Using db = New SQLProvider()
            Using re = db.ExecuteReader(strSql)
                While re.Read()
                    Dim obj = New MonHocDTO
                    obj.MAMON = re.GetInt32(0)
                    obj.KYHIEUMON = re.GetString(1)
                    obj.TENMON = re.GetString(2)
                    ds.Add(obj)
                End While
            End Using
        End Using

        Return ds
    End Function

    Public Shared Function DemSoGVTheoMon() As List(Of MonHocDTO)
        Dim str = "select m.MAMON, KYHIEUMON, TENMON, count(g.MAGIAOVIEN) "
        str &= "from MONHOC m, GIAOVIEN g where m.MAMON=g.MAMON "
        str &= "group by m.MAMON, KYHIEUMON, TENMON"
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of MonHocDTO)

        While reader.Read()
            Dim obj As New MonHocDTO
            obj.MAMON = reader.GetInt32(0)
            obj.KYHIEUMON = reader.GetString(1)
            obj.TENMON = reader.GetString(2)
            obj.SLGIAOVIEN = reader.GetInt32(3)

            ds.Add(obj)
        End While
        conn.Close()
        Return ds
    End Function
End Class
